package newsSystem.dao;

import newsSystem.Order.OrderInfo;

import newsSystem.Order.OrderManager;
import newsSystem.utils.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class OrderInfoJDBC {
//    显示所有订单信息操作
    public static void orderJDBC() {
//public static void main(String[] args) {
//    调用Util里面连接数据库中的类
        Connection connection = null;
        ResultSet resultSet = null;
        Statement statement = null;
        try {
            // 获取数据库连接
            connection = DBUtil.getConnection();

            // 定义 SQL 查询语句
            String sql = "SELECT * FROM orderinfo";

            // 创建 Statement 对象执行查询
            statement = connection.createStatement();

            // 执行查询并获取结果集合
            resultSet = statement.executeQuery(sql);
//         创建一个管理员list列表对象
            List<OrderInfo> OrderList=new ArrayList<>();
            // 处理结果集合
            while (resultSet.next()) {
                // 获取每一行数据的具体字段值
              int OrderNo = resultSet.getInt("OrderNo");
                String OrderAmount = resultSet.getString("OrderAmount");
                String OrderMonth = resultSet.getString("OrderMonth");
                String OrderPrice = resultSet.getString("OrderPrice");
               Date OrderDate = resultSet.getDate("OrderDate");
                String UserID = resultSet.getString("UserID");
                String  AdminID = resultSet.getString("AdminID");
                String  NewsID = resultSet.getString("NewsID");

                // 打印结果
//                System.out.println("OrderNo: " + resultSet.getInt("OrderNo") + ", " +
//                        "OrderAmount: " + resultSet.getString("OrderAmount") + ", " +
//                        "OrderMonth: " + resultSet.getString("OrderMonth") + ", " +
//                        "OrderPrice: " + resultSet.getString("OrderPrice") + ", " +
//                        "OrderDate: " + resultSet.getDate("OrderDate") + ", " +
//                        "UserID: " + resultSet.getString("UserID") + ", " +
//                        "AdminID: " + resultSet.getString("AdminID") + ", " +
//                        "NewsID: " + resultSet.getString("NewsID"));

//            创建管理员对象并且将其添加到集合中去
                OrderInfo order=new OrderInfo(OrderNo,OrderAmount,OrderMonth,OrderPrice,OrderDate,UserID,AdminID,NewsID);
//                调用OrderManager类中的添加add方法加入到列表中
                OrderManager.addOrder(order);
            }

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
//            连接结束后释放资源
            DBUtil.closeConnection(connection);
//            释放结果集合
            DBUtil.closeResultSet(resultSet);
//            释放Statement 对象
            DBUtil.closePreparedStatement(statement);
        }
    }
    public static void getOrderInfoByUserID(String userID) {
        Connection connection = null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        try {
            // 获取数据库连接
            connection = DBUtil.getConnection();

            // 定义 SQL 查询语句，根据 UserID 进行过滤
            String sql = "SELECT * FROM orderinfo WHERE UserID = ?";

            // 使用 PreparedStatement 预编译 SQL 语句
            preparedStatement = connection.prepareStatement(sql);
            // 设置参数值，替换 SQL 语句中的占位符 "?"
            preparedStatement.setString(1, userID);

            // 执行查询并获取结果集合
            resultSet = preparedStatement.executeQuery();

            // 处理结果集合
            while (resultSet.next()) {
                int orderNo = resultSet.getInt("OrderNo");
                String orderAmount = resultSet.getString("OrderAmount");
                String orderMonth = resultSet.getString("OrderMonth");
                String orderPrice = resultSet.getString("OrderPrice");
                java.util.Date orderDate = resultSet.getDate("OrderDate");
                String adminID = resultSet.getString("AdminID");
                String newsID = resultSet.getString("NewsID");

                // 创建订单对象
                OrderInfo order = new OrderInfo(orderNo, orderAmount, orderMonth, orderPrice, orderDate, userID, adminID, newsID);

                // 将订单对象添加到 OrderList 中
                OrderManager.addOrder(order);
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            DBUtil.closeConnection(connection);
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
        }
    }
//    添加订单信息操作
    public static void saveOrderToDatabase(OrderInfo order) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 获取数据库连接
            connection = DBUtil.getConnection();

            // 定义 SQL 插入语句
            String sql = "INSERT INTO orderinfo (OrderNo, OrderAmount, OrderMonth, OrderPrice, OrderDate, UserID, AdminID, NewsID) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

            // 使用 PreparedStatement 预编译 SQL 语句
            preparedStatement = connection.prepareStatement(sql);

            // 设置参数值
            preparedStatement.setInt(1, order.getOrderNo());
            preparedStatement.setString(2, order.getOrderAmount());
            preparedStatement.setString(3, order.getOrderMonth());
            preparedStatement.setString(4, order.getOrderPrice());
            preparedStatement.setDate(5, new java.sql.Date(order.getOrderDate().getTime()));
            preparedStatement.setString(6, order.getUserID());
            preparedStatement.setString(7, order.getAdminID());
            preparedStatement.setString(8, order.getNewsID());

            // 执行插入操作
            preparedStatement.executeUpdate();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            DBUtil.closeConnection(connection);
            DBUtil.closePreparedStatement(preparedStatement);
        }
    }
//    删除订单操作
    public static void deleteOrderFromDatabase(int orderNo) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            // 获取数据库连接
            connection = DBUtil.getConnection();

            // 定义 SQL 删除语句
            String sql = "DELETE FROM orderinfo WHERE OrderNo = ?";

            // 创建 PreparedStatement 对象，并设置参数
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, orderNo);

            // 执行删除操作
            int rowsAffected = preparedStatement.executeUpdate();

            // 输出删除结果
            if (rowsAffected > 0) {
                System.out.println("订单已成功删除。");
            } else {
                System.out.println("未找到匹配的订单。");
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            DBUtil.closeConnection(connection);
            DBUtil.closePreparedStatement(preparedStatement);
        }
    }
//更新订单操作
public static void updateOrderInDatabase(OrderInfo order) {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try {
        // 获取数据库连接
        connection = DBUtil.getConnection();

        // 定义 SQL 更新语句
        String sql = "UPDATE orderinfo SET OrderAmount=?, OrderMonth=?, OrderPrice=?, OrderDate=?, AdminID=?, NewsID=? WHERE OrderNo=? AND UserID=?";

        // 使用 PreparedStatement 预编译 SQL 语句
        preparedStatement = connection.prepareStatement(sql);

        // 设置参数值
        preparedStatement.setString(1, order.getOrderAmount());
        preparedStatement.setString(2, order.getOrderMonth());
        preparedStatement.setString(3, order.getOrderPrice());
        preparedStatement.setDate(4, new java.sql.Date(order.getOrderDate().getTime()));
        preparedStatement.setString(5, order.getAdminID());
        preparedStatement.setString(6, order.getNewsID());
        preparedStatement.setInt(7, order.getOrderNo());
        preparedStatement.setString(8, order.getUserID());

        // 执行更新操作
        preparedStatement.executeUpdate();
    } catch (ClassNotFoundException | SQLException e) {
        e.printStackTrace();
    } finally {
        // 释放资源
        DBUtil.closeConnection(connection);
        DBUtil.closePreparedStatement(preparedStatement);
    }
}


}
